﻿using HtmlAgilityPack;
using Newtonsoft.Json;
using Sunny.UI;
using System;
using System.Collections;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.SqlClient;
using System.Diagnostics;
using System.Drawing;
using System.IO;
using System.Linq;
using System.Reflection;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
//using System.Windows.Forms;

namespace DataConllection
{
    public partial class frmMain : UIForm
    {
        DataTable dtable = new DataTable("TableName");
        public static string ConnectString = "Server=.;Initial Catalog=FSTHBPT;Integrated Security=true";
        public frmMain()
        {
            InitializeComponent();
        } 

        /// <summary>
        /// 抓取数据按钮操作
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void btnStart_Click(object sender, EventArgs e)
        {
            //每次点击 都会清空一遍datagridview数据  保证每次抓取到的都是最新数据
            this.uiDataGridView1.Rows.Clear();

            if (string.IsNullOrEmpty(this.txtNet.Text))
            {
                ShowErrorTip("噢!该死!你不输入网址,我抓毛的数据?");
                return;
            }
            string url = this.txtNet.Text;
            #region 单一特定网址  "https://www.cnblogs.com/"
            List<Info> blog = new List<Info>();
            HtmlWeb web = new HtmlWeb();
            HtmlNodeCollection node;

            node = web.Load(url).DocumentNode.SelectNodes("//a[@class='post-item-title']");
            foreach (var item in node)
            {
                blog.Add(new Info
                {
                    Title = item.InnerText,
                    Content = web.Load(item.GetAttributeValue("href", "")).DocumentNode.SelectSingleNode("//div[@id='cnblogs_post_body']").InnerText
                });
            }
            //循环赋值 使数据呈现在gridview上
            foreach (var item1 in blog)
            {
                DataGridViewRow dv = new DataGridViewRow();
                dv.CreateCells(this.uiDataGridView1);
                dv.Cells[0].Value = item1.Title.ToString();
                dv.Cells[1].Value = item1.Content.ToString();
                this.uiDataGridView1.Rows.Add(dv);
            }            

            //创建传入对象名称的列
            foreach (var item in blog.FirstOrDefault().GetType().GetProperties())
            {
                dtable.Columns.Clear();//每次都清空一遍DataTable 防止异常
                dtable.Columns.Add(item.Name);
            }
            //循环存储
            foreach (var item in blog)
            {
                //新加行
                DataRow value = dtable.NewRow();
                //根据DataTable中的值，进行对应的赋值
                foreach (DataColumn dtColumn in dtable.Columns)
                {
                    int i = dtable.Columns.IndexOf(dtColumn);
                    //基元元素，直接复制，对象类型等，进行序列化
                    if (value.GetType().IsPrimitive)
                    {
                        value[i] = item.GetType().GetProperty(dtColumn.ColumnName).GetValue(item);
                    }
                    else
                    {
                        value[i] = JsonConvert.SerializeObject(item.GetType().GetProperty(dtColumn.ColumnName).GetValue(item));
                    }
                }
                dtable.Rows.Add(value);
            }
            #endregion

            #region 任意网址

            #endregion

            #region 计算抓取数据耗时
            int sum = 0;
            //Stopwatch提供一组方法和属性，可用于准确地测量运行时间
            Stopwatch sw = new Stopwatch();
            sw.Start();
            for (int i = 0; i < 100000; i++)
            {
                sum += i;
            }
            sw.Stop();
            //获取当前实例测量得出的总运行时间
            TimeSpan dt = sw.Elapsed;
            ShowInfoTip("耗时:" + dt.ToString());
            #endregion
        }

        /// <summary>
        /// 导出Excel按钮操作
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void btnImportExcel_Click(object sender, EventArgs e)
        {
            if (this.uiDataGridView1.Rows.Count<=0)
            {
                ShowErrorTip("噢!别这样!没数据怎么导出Excel?");
                return;
            }
            string path = AppDomain.CurrentDomain.BaseDirectory + @"" + DateTime.Now.ToString("yyyyMMddHHmmss") + ".xls";
            if (dt2xls(dtable, path, "爬出来的数据", "标题,内容"))
            {
                ShowInfoDialog("导出成功,文件位置就在:" + path);
            }
            else
            {
                ShowErrorTip("导出失败,具体原因自查!");
            }
        }

        /// <summary>
        /// 导出报表为xls
        /// </summary>
        /// <param name="dt">DataTable</param>
        /// <param name="strFilePath">物理路径</param>
        /// <param name="tableheader">表头</param>
        /// <param name="columname">字段标题,逗号分隔</param>
        public static bool dt2xls(DataTable dt, string strFilePath, string tableheader, string columname)
        {
            try
            {
                string strBufferLine = "";
                StreamWriter strmWriterObj = new StreamWriter(strFilePath, false, System.Text.Encoding.UTF8);
                strmWriterObj.WriteLine(tableheader);
                strmWriterObj.WriteLine(columname);
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    strBufferLine = "";
                    for (int j = 0; j < dt.Columns.Count; j++)
                    {
                        if (j > 0)
                            strBufferLine += ",";
                        strBufferLine += dt.Rows[i][j].ToString();
                    }
                    strmWriterObj.WriteLine(strBufferLine);
                }
                strmWriterObj.Close();
                return true;
            }
            catch
            {
                return false;
            }
        }

        #region 辅助方法
        /// <summary>
        /// 绑定DataGridView数据到DataTable
        /// </summary>
        /// <param name="dgv">复制数据的DataGridView</param>
        /// <returns>返回的绑定数据后的DataTable</returns>
        public DataTable GetDgvToTable(DataGridView dgv)
        {
            DataTable dt = new DataTable();
            // 列强制转换
            for (int count = 0; count < dgv.Columns.Count; count++)
            {
                DataColumn dc = new DataColumn(dgv.Columns[count].Name.ToString());
                dt.Columns.Add(dc);
            }
            // 循环行
            for (int count = 0; count < dgv.Rows.Count; count++)
            {
                DataRow dr = dt.NewRow();
                for (int countsub = 0; countsub < dgv.Columns.Count; countsub++)
                {
                    dr[countsub] = Convert.ToString(dgv.Rows[count].Cells[countsub].Value);
                }
                dt.Rows.Add(dr);
            }
            return dt;
        }

        /// <summary>
        /// 将DataGridViewRows转换为DataTable数据
        /// </summary>
        /// <param name="Listrows">DataGridView的行集合</param>
        /// <param name="dtSource">源数据表</param>
        /// <param name="colum">映射关系</param>
        /// <param name="dtDestination">目的数据表</param>
        /// <returns>返回新的DataTable</returns>
        public static DataTable Getdatatable(DataGridViewRowCollection Listrows, DataTable dtSource, string[,] colum, DataTable dtDestination)
        {
            //  string[,] colum = { { "id1", "id2" }, { "name1", "name2" } };  //colum是源DataGridViewRow到目标DataRow的映射关系
            DataTable dtNew = dtSource.Clone();//复制表结构
            foreach (DataGridViewRow dgvr in Listrows)
            {
                DataRow dataRow = (dgvr.DataBoundItem as DataRowView).Row;  //转换成数据行DataRow
                dtNew.ImportRow(dataRow);  //添加DataRow到DataTable
            }
            string[] copy = new string[colum.Length / 2];
            for (int i = 0; i < colum.Length / 2; i++)
            {
                copy[i] = colum[i, 0];  //获取源数据要的列
            }
            DataTable dtNew2 = dtNew.DefaultView.ToTable(false, copy);//复制源表结构和数据，并只保留copy数据中的列
            if (dtDestination != null)
            {
                for (int j = 0; j < dtNew2.Rows.Count; j++)
                {
                    DataRow row = dtDestination.NewRow();
                    for (int f = 0; f < colum.Length / 2; f++)
                    {
                        row[colum[f, 1]] = dtNew2.Rows[j][colum[f, 0]]; //将源表中每行的各列数据复制到对应的目的表的行中
                    }
                    dtDestination.Rows.Add(row); //添加到目的表
                }
            }
            return dtDestination;
        }

        /// <summary>
        /// List转DataTable
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="collection"></param>
        /// <returns></returns>
        public static DataTable ToDataTable<T>(IEnumerable<T> collection)
        {
            var props = typeof(T).GetProperties();
            var dt = new DataTable();
            dt.Columns.AddRange(props.Select(p => new DataColumn(p.Name, p.PropertyType)).ToArray());
            if (collection.Count() > 0)
            {
                for (int i = 0; i < collection.Count(); i++)
                {
                    ArrayList tempList = new ArrayList();
                    foreach (PropertyInfo pi in props)
                    {
                        object obj = pi.GetValue(collection.ElementAt(i), null);
                        tempList.Add(obj);
                    }
                    object[] array = tempList.ToArray();
                    dt.LoadDataRow(array, true);
                }
            }
            return dt;
        }
        #endregion

        #region 消息提示框
        /// <summary>
        /// 正确信息提示框
        /// </summary>
        /// <param name="msg">信息</param>
        /// <param name="showMask">显示遮罩层</param>
        public void ShowSuccessDialog(string msg, bool showMask = true)
        {
            UIMessageDialog.ShowMessageDialog(msg, UILocalize.SuccessTitle, false, UIStyle.Green, showMask);
        }

        /// <summary>
        /// 信息提示框
        /// </summary>
        /// <param name="msg">信息</param>
        /// <param name="showMask">显示遮罩层</param>
        public void ShowInfoDialog(string msg, bool showMask = true)
        {
            UIMessageDialog.ShowMessageDialog(msg, UILocalize.InfoTitle, false, UIStyle.Gray, showMask);
        }

        /// <summary>
        /// 警告信息提示框
        /// </summary>
        /// <param name="msg">信息</param>
        /// <param name="showMask">显示遮罩层</param>
        public void ShowWarningDialog(string msg, bool showMask = true)
        {
            UIMessageDialog.ShowMessageDialog(msg, UILocalize.WarningTitle, false, UIStyle.Orange, showMask);
        }

        /// <summary>
        /// 错误信息提示框
        /// </summary>
        /// <param name="msg">信息</param>
        /// <param name="showMask">显示遮罩层</param>
        public void ShowErrorDialog(string msg, bool showMask = true)
        {
            UIMessageDialog.ShowMessageDialog(msg, UILocalize.ErrorTitle, false, UIStyle.Red, showMask);
        }

        /// <summary>
        /// 确认信息提示框
        /// </summary>
        /// <param name="msg">信息</param>
        /// <param name="showMask">显示遮罩层</param>
        /// <returns>结果</returns>
        public bool ShowAskDialog(string msg, bool showMask = true)
        {
            return UIMessageDialog.ShowMessageDialog(msg, UILocalize.AskTitle, true, UIStyle.Blue, showMask);
        }

        /// <summary>
        /// 正确信息提示框
        /// </summary>
        /// <param name="title">标题</param>
        /// <param name="msg">信息</param>
        /// <param name="style">主题</param>
        /// <param name="showMask">显示遮罩层</param>
        public void ShowSuccessDialog(string title, string msg, UIStyle style = UIStyle.Green, bool showMask = true)
        {
            UIMessageDialog.ShowMessageDialog(msg, title, false, style, showMask);
        }

        /// <summary>
        /// 信息提示框
        /// </summary>
        /// <param name="title">标题</param>
        /// <param name="msg">信息</param>
        /// <param name="style">主题</param>
        /// <param name="showMask">显示遮罩层</param>
        public void ShowInfoDialog(string title, string msg, UIStyle style = UIStyle.Gray, bool showMask = true)
        {
            UIMessageDialog.ShowMessageDialog(msg, title, false, style, showMask);
        }

        /// <summary>
        /// 警告信息提示框
        /// </summary>
        /// <param name="title">标题</param>
        /// <param name="msg">信息</param>
        /// <param name="style">主题</param>
        /// <param name="showMask">显示遮罩层</param>
        public void ShowWarningDialog(string title, string msg, UIStyle style = UIStyle.Orange, bool showMask = true)
        {
            UIMessageDialog.ShowMessageDialog(msg, title, false, style, showMask);
        }

        /// <summary>
        /// 错误信息提示框
        /// </summary>
        /// <param name="title">标题</param>
        /// <param name="msg">信息</param>
        /// <param name="style">主题</param>
        /// <param name="showMask">显示遮罩层</param>
        public void ShowErrorDialog(string title, string msg, UIStyle style = UIStyle.Red, bool showMask = true)
        {
            UIMessageDialog.ShowMessageDialog(msg, title, false, style, showMask);
        }

        /// <summary>
        /// 确认信息提示框
        /// </summary>
        /// <param name="title">标题</param>
        /// <param name="msg">信息</param>
        /// <param name="style">主题</param>
        /// <param name="showMask">显示遮罩层</param>
        /// <returns>结果</returns>
        public bool ShowAskDialog(string title, string msg, UIStyle style = UIStyle.Blue, bool showMask = true)
        {
            return UIMessageDialog.ShowMessageDialog(msg, title, true, style, showMask);
        }

        /// <summary>
        /// 显示消息
        /// </summary>
        /// <param name="text">消息文本</param>
        /// <param name="delay">消息停留时长(ms)。默认1秒</param>
        /// <param name="floating">是否漂浮</param>
        public void ShowInfoTip(string text, int delay = 1000, bool floating = true)
            => UIMessageTip.Show(text, null, delay, floating);

        /// <summary>
        /// 显示成功消息
        /// </summary>
        /// <param name="text">消息文本</param>
        /// <param name="delay">消息停留时长(ms)。默认1秒</param>
        /// <param name="floating">是否漂浮</param>
        public void ShowSuccessTip(string text, int delay = 1000, bool floating = true)
            => UIMessageTip.ShowOk(text, delay, floating);

        /// <summary>
        /// 显示警告消息
        /// </summary>
        /// <param name="text">消息文本</param>
        /// <param name="delay">消息停留时长(ms)。默认1秒</param>
        /// <param name="floating">是否漂浮</param>
        public void ShowWarningTip(string text, int delay = 1000, bool floating = true)
            => UIMessageTip.ShowWarning(text, delay, floating);

        /// <summary>
        /// 显示出错消息
        /// </summary>
        /// <param name="text">消息文本</param>
        /// <param name="delay">消息停留时长(ms)。默认1秒</param>
        /// <param name="floating">是否漂浮</param>
        public void ShowErrorTip(string text, int delay = 1000, bool floating = true)
            => UIMessageTip.ShowError(text, delay, floating);

        /// <summary>
        /// 在指定控件附近显示消息
        /// </summary>
        /// <param name="controlOrItem">控件或工具栏项</param>
        /// <param name="text">消息文本</param>
        /// <param name="delay">消息停留时长(ms)。默认1秒</param>
        /// <param name="floating">是否漂浮</param>
        public void ShowInfoTip(Component controlOrItem, string text, int delay = 1000, bool floating = true)
            => UIMessageTip.Show(controlOrItem, text, null, delay, floating);

        /// <summary>
        /// 在指定控件附近显示良好消息
        /// </summary>
        /// <param name="controlOrItem">控件或工具栏项</param>
        /// <param name="text">消息文本</param>
        /// <param name="delay">消息停留时长(ms)。默认1秒</param>
        /// <param name="floating">是否漂浮</param>
        public void ShowSuccessTip(Component controlOrItem, string text, int delay = 1000, bool floating = true)
            => UIMessageTip.ShowOk(controlOrItem, text, delay, floating);

        /// <summary>
        /// 在指定控件附近显示出错消息
        /// </summary>
        /// <param name="controlOrItem">控件或工具栏项</param>
        /// <param name="text">消息文本</param>
        /// <param name="delay">消息停留时长(ms)。默认1秒</param>
        /// <param name="floating">是否漂浮</param>
        public void ShowErrorTip(Component controlOrItem, string text, int delay = 1000, bool floating = true)
            => UIMessageTip.ShowError(controlOrItem, text, delay, floating);

        /// <summary>
        /// 在指定控件附近显示警告消息
        /// </summary>
        /// <param name="controlOrItem">控件或工具栏项</param>
        /// <param name="text">消息文本</param>
        /// <param name="delay">消息停留时长(ms)。默认1秒</param>
        /// <param name="floating">是否漂浮</param>
        public void ShowWarningTip(Component controlOrItem, string text, int delay = 1000, bool floating = true)
            => UIMessageTip.ShowWarning(controlOrItem, text, delay, floating, false);
        #endregion

        private void btImportSQL_Click(object sender, EventArgs e)
        {
            if (string.IsNullOrEmpty(this.txtNet.Text))
            {
                ShowErrorTip("噢!该死!你不输入网址,我如何导入数据库?");
                return;
            }
            string url = this.txtNet.Text;
            List<Info> blog = new List<Info>();
            HtmlWeb web = new HtmlWeb();
            HtmlNodeCollection node;

            node = web.Load(url).DocumentNode.SelectNodes("//a[@class='post-item-title']");
            foreach (var item in node)
            {
                blog.Add(new Info
                {
                    Title = item.InnerText,
                    Content = web.Load(item.GetAttributeValue("href", "")).DocumentNode.SelectSingleNode("//div[@id='cnblogs_post_body']").InnerText
                });
            }

            string sql = "select MsgTitle,MsgContent from Msg";
            using (SqlConnection conn=new SqlConnection(ConnectString)) 
            {
                using (SqlCommand cmd = new SqlCommand(sql,conn)) 
                {
                    conn.Open();
                    using (SqlDataReader sdr = cmd.ExecuteReader())
                    {
                        if (sdr.Read()) 
                        {
                            string title = sdr.GetString(0).Trim();
                            string content = sdr.GetString(1).Trim();
                        }
                    }
                }
            }
        }
    }
}
